/**
 * 
 */
package org.project.product.android.pfe.fanta.data;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.project.product.android.pfe.fanta.domain.Category;
import org.project.product.android.pfe.fanta.domain.Product;
import org.project.product.android.pfe.fanta.domain.User;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author JBromo
 * 
 */
public class SqlHelper extends SQLiteOpenHelper {

	private static SqlHelper helper;
	SQLiteDatabase db;

	public static SqlHelper getInstance(Context context) {
		if (helper == null) {
			helper = new SqlHelper(context);
		}
		return helper;
	}

	public SqlHelper(Context context) {
		super(context, "fanta_pfe.db", null, 1);
		db = getWritableDatabase();

	}

	public List<Category> getCategoryFromLocal() {
		Cursor c = db.query("category", null, null, null, null, null, null);
		List<Category> categories = new ArrayList<Category>();
		while (c != null && c.moveToNext()) {
			int id = c.getInt(0);
			String label = c.getString(1);
			String description = c.getString(2);
			Category category = new Category();
			category.setId(id);
			category.setName(label);
			category.setDescription(description);
			// TODO use jointure to retrieve number product by item
			List<Product> product = getProductByCategoryFromLocal(category);
			category.setNumberItem(product.size());
			categories.add(category);
		}

		Collections.sort(categories);
		return categories;
	}

	public List<Product> getAllProduct() {
		return getProductByCategoryFromLocal(null);
	}

	public int getProductNumber(String productName) {
		Cursor c = db.query("product", new String[] { "id" }, "nom =  ?",
		        new String[] { String.valueOf(productName) }, null, null, null);
		return c.getCount();

	}

	public List<Product> getProductByCategoryFromLocal(Category category) {
		Cursor c = db.query("product", null, "idcategory =  ?",
		        new String[] { String.valueOf(category.getId()) }, null, null,
		        null);
		List<Product> products = new ArrayList<Product>();
		while (c != null && c.moveToNext()) {
			Product product = createProductFromCursor(c);
			if (!products.contains(product)) {
				products.add(product);
			}
		}

		return products;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		db.execSQL("CREATE TABLE IF NOT EXISTS category(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, description TEXT)");
		db.execSQL("CREATE TABLE IF NOT EXISTS product(id INTEGER PRIMARY KEY AUTOINCREMENT, nom TEXT UNIQUE, codebarre TEXT , idcategory TEXT , poids TEXT , description TEXT, quantity TEXT, prix TEXT)");
		db.execSQL("CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY AUTOINCREMENT,matricule TEXT, login TEXT, password TEXT, codepin TEXT)");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public long saveCategory(Category category) {
		ContentValues values = new ContentValues();
		values.put("name", category.getName());
		values.put("description", category.getDescription());
		return db.insert("category", null, values);
	}

	public long saveCategory(Category category, int id) {
		ContentValues values = new ContentValues();
		values.put("id", id);
		values.put("name", category.getName());
		values.put("description", category.getDescription());
		return db.insert("category", null, values);
	}

	public void deleteCategory(Category category) {
		db.delete("category", "id= " + category.getId(), null);

	}

	public void deleteProduit(Product product) {
		Log.i("Delete", product.toString());
		int result = db.delete("product", "id= " + product.getId(), null);

		Log.i("Delete", result + product.toString());

	}

	public long saveProduit(Product product) {
		int result = db.delete("product", "id= " + product.getId(), null);
		Log.w("Product to delete", result + product.toString());
		ContentValues values = productContentValue(product);
		return db.insert("product", null, values);
	}

	private ContentValues productContentValue(Product product) {
		ContentValues values = new ContentValues();
		values.put("nom", product.getNom());
		values.put("codebarre", product.getCodBarre());
		values.put("idcategory", product.getIdCategory());
		values.put("poids", product.getPoids());
		values.put("description", product.getDescription());
		values.put("quantity", product.getQuantity());
		values.put("prix", product.getPrix());
		return values;
	}

	public void saveUser(User user) {

		db.delete("user", null, null);
		ContentValues values = new ContentValues();
		values.put("matricule", user.getMatricule());
		values.put("login", user.getLogin());
		values.put("password", user.getPassword());
		values.put("codepin", user.getCodePin());
		db.insert("user", null, values);
	}

	public boolean isValidUser(User user) {
		String request = "select login,password from user where login =? and password=?";
		Cursor cursor = db.rawQuery(request, new String[] { user.getLogin(),
		        user.getPassword() });
		return cursor != null && cursor.getCount() > 0;
	}

	public User getUserFromLocal() {
		Cursor c = db.query("user", null, null, null, null, null, null);
		while (c != null && c.moveToNext()) {
			int id = c.getInt(0);
			String matricule = c.getString(1);
			String login = c.getString(2);
			String password = c.getString(3);
			User user = new User();
			user.setId(id);
			user.setLogin(login);
			user.setMatricule(matricule);
			user.setPassword(password);
			user.setCodePin(c.getString(4));
			return user;
		}

		return null;
	}

	public boolean haveAlreadyUserAccount() {
		Cursor c = db.query("user", null, null, null, null, null, null);
		while (c != null && c.moveToNext()) {
			return true;
		}

		return false;
	}

	public User retrieveUserByCodePin(String codePin) {
		Cursor c = db.query("user", null, "codepin =  ?",
		        new String[] { codePin }, null, null, null);
		if (c != null && c.moveToNext()) {
			int id = c.getInt(0);
			String matricule = c.getString(1);
			String login = c.getString(2);
			String password = c.getString(3);
			User user = new User();
			user.setId(id);
			user.setLogin(login);
			user.setMatricule(matricule);
			user.setPassword(password);
			user.setCodePin(c.getString(4));
			return user;
		}

		return null;
	}

	public void deleteUser(User userSelected) {
		db.delete("user", "id= " + userSelected.getId(), null);

	}

	public Product searchProductByEan(String ean) {

		Cursor c = db.query("product", null, "codebarre =  ?",
		        new String[] { ean }, null, null, null);
		if (c != null && c.moveToNext()) {
			Product product = createProductFromCursor(c);
			return product;
		}

		return null;
	}

	private Product createProductFromCursor(Cursor c) {
		int id = c.getInt(0);
		String nom = c.getString(1);
		String codeBarre = c.getString(2);
		int idcategory = c.getInt(3);
		double poids = c.getDouble(4);
		String description = c.getString(5);

		Product product = new Product();
		product.setId(id);
		product.setCodBarre(codeBarre);
		product.setIdCategory(idcategory);
		product.setNom(nom);
		product.setPoids(poids);
		product.setDescription(description);
		product.setQuantity(c.getInt(6));
		product.setPrix(c.getDouble(7));
		return product;
	}

	public Product searchProductByName(String name) {

		Cursor c = db.query("product", null, "nom =  ?", new String[] { name },
		        null, null, null);
		if (c != null && c.moveToNext()) {
			Product product = createProductFromCursor(c);
			return product;
		}

		return null;
	}

	public void deleteProduit(Product product, int quantity) {

		Product oldProduct = searchProductById(product.getId());
		int oldQuantity = oldProduct.getQuantity();
		int quantityResult = oldQuantity - quantity;
		if (quantityResult < 1) {
			deleteProduit(product);
		} else {
			oldProduct.setQuantity(quantityResult);
			updateProductQuantity(oldProduct);

		}

	}

	public void updateProductQuantity(Product product) {
		db.update("product", productContentValue(product),
		        "id= " + product.getId(), null);

	}

	public void updateCategory(Category category) {
		String filter = "id  = " + category.getId();
		ContentValues values = new ContentValues();
		values.put("name", category.getName());
		values.put("description", category.getDescription());
		db.update("category", values, filter, null);

	}

	public Product searchProductById(int id) {

		Cursor c = db.query("product", null, "id =  ?",
		        new String[] { String.valueOf(id) }, null, null, null);
		if (c != null && c.moveToNext()) {
			Product product = createProductFromCursor(c);
			return product;
		}

		return null;
	}

}
